package com.monlyu.bo;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Repository;

import com.monlyu.bean.Pages;
import com.monlyu.dao.SqlDao;
import com.monlyu.listener.InitDataListener;
import com.monlyu.util.StringUtil;
import com.monlyu.util.WebOptUtil;

@Repository(value = "sqlBo")
public class SqlBo extends BaseBo {

	private SqlDao sqlDao;
	public void setSqlDao(SqlDao sqlDao) {
		this.sqlDao = sqlDao;
	}

	public int excuteSql(String sql) {
		String[] strs = sql.split(";");
		int total = 0;
		//SqlDao sqlDao = (SqlDao) InitDataListener.getBeanById("sqlDao");
		for (int i = 0; i < strs.length; i++) {
			if (!StringUtil.isEmpty(strs[i])) {
				total += sqlDao.excute(strs[i], new Object[] {});
			}
		}
		return total;
	}

	public Pages getResultsBySql(String sql, int pageNo, int pageSize) {
		return ((SqlDao) InitDataListener.getBeanById("sqlDao")).find(sql, pageNo, pageSize);
	}

	public String getExportName() throws Exception {
		return new String("查询结果.xls".getBytes(), "ISO-8859-1");
	}

	public InputStream getExportInputStream(String sql) throws Exception {
		SqlDao helperDao = (SqlDao) InitDataListener.getBeanById("sqlDao");
		String[] heads = helperDao.getColumns(sql);
		List results = helperDao.find(sql);
		ByteArrayOutputStream os = createReport(heads, results);
		return new ByteArrayInputStream(os.toByteArray());
	}

	private ByteArrayOutputStream createReport(String[] heads, List results) throws Exception {
		String url = WebOptUtil.APP_ARGS.getWebFileDir() + "common/report/sqlQueryResult.xls";
		InputStream is = new FileInputStream(url);
		HSSFWorkbook wb = new HSSFWorkbook(is);
		is.close();
		HSSFSheet sheet = wb.getSheetAt(0);
		setHeaders(heads, sheet);
		setBody(results, sheet);
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		wb.write(os);
		return os;
	}

	private void setHeaders(String[] heads, HSSFSheet sheet) {
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = null;
		for (int i = 0; i < heads.length; i++) {
			cell = row.createCell((short) i);
			cell.setCellValue(new HSSFRichTextString(heads[i]));
		}
	}

	private void setBody(List results, HSSFSheet sheet) {
		int index = 1;
		HSSFRow row = null;
		HSSFCell cell = null;
		for (Iterator it = results.iterator(); it.hasNext();) {
			final Object obj = it.next();
			Object[] objects = (obj instanceof Object[]) ? (Object[]) obj : new Object[] { obj };
			row = sheet.createRow(index);
			for (int i = 0; i < objects.length; i++) {
				cell = row.createCell((short) i);
				cell.setCellValue(new HSSFRichTextString(objects[i] != null ? objects[i].toString() : ""));
			}
			index++;
		}
	}

}
